{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas 数据类型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         Date Type   Value\n",
      "0  2016-03-01    a  11.432\n",
      "1  2016-03-02    b  13.031\n",
      "2  2016-03-01    c  20.784\n",
      "3  2016-03-03    a  99.906\n",
      "4  2016-03-02    a   1.303\n",
      "5  2016-03-03    c  20.784\n",
      "Type             a       b       c\n",
      "Date                              \n",
      "2016-03-01  11.432     NaN  20.784\n",
      "2016-03-02   1.303  13.031     NaN\n",
      "2016-03-03  99.906     NaN  20.784\n"
     ]
    }
   ],
   "source": [
    "s = pd.Series([3, -5, 7, 4], index=['a', 'b', 'c', 'd'])\n",
    "\n",
    "data = {'Country': ['Belgium', 'India', 'Brazil'],\n",
    "'Capital': ['Brussels', 'New Delhi', 'Brasília'],\n",
    "'Population': [11190846, 1303171035, 207847528]}\n",
    "df = pd.DataFrame(data,\n",
    "columns=['Country', 'Capital', 'Population'])\n",
    "\n",
    "#Pivvot,\n",
    "data = {'Date': ['2016-03-01', '2016-03-02', '2016-03-01','2016-03-03','2016-03-02',\n",
    "                 '2016-03-03'],\n",
    "'Type': ['a', 'b', 'c','a','a','c'],\n",
    "'Value': [11.432, 13.031, 20.784,99.906,1.303,20.784]}\n",
    "df2 = pd.DataFrame(data,\n",
    "columns=['Date', 'Type', 'Value'])\n",
    "df3= df2.pivot(index='Date',\n",
    "columns='Type',\n",
    "values='Value')\n",
    "print df2\n",
    "print df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         Date Type   Value\n",
      "0  2016-03-01    a  11.432\n",
      "1  2016-03-02    b  13.031\n",
      "2  2016-03-01    c  20.784\n",
      "3  2016-03-03    a  99.906\n",
      "4  2016-03-02    a   1.303\n",
      "5  2016-03-03    c  20.784\n",
      "Type             a       b       c\n",
      "Date                              \n",
      "2016-03-01  11.432     NaN  20.784\n",
      "2016-03-02   1.303  13.031     NaN\n",
      "2016-03-03  99.906     NaN  20.784\n"
     ]
    }
   ],
   "source": [
    "df4 = pd.pivot_table(df2,values='Value',index='Date',columns=['Type'])\n",
    "print df2\n",
    "print df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Type             a       b       c\n",
      "Date                              \n",
      "2016-03-01  11.432     NaN  20.784\n",
      "2016-03-02   1.303  13.031     NaN\n",
      "2016-03-03  99.906     NaN  20.784\n"
     ]
    }
   ],
   "source": [
    "df4 = pd.pivot_table(df2,\n",
    "values='Value',\n",
    "index='Date',\n",
    "columns=['Type'])\n",
    "print df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          Date variable Observations\n",
      "0   2016-03-01     Type            a\n",
      "1   2016-03-02     Type            b\n",
      "2   2016-03-01     Type            c\n",
      "3   2016-03-03     Type            a\n",
      "4   2016-03-02     Type            a\n",
      "5   2016-03-03     Type            c\n",
      "6   2016-03-01    Value       11.432\n",
      "7   2016-03-02    Value       13.031\n",
      "8   2016-03-01    Value       20.784\n",
      "9   2016-03-03    Value       99.906\n",
      "10  2016-03-02    Value        1.303\n",
      "11  2016-03-03    Value       20.784\n"
     ]
    }
   ],
   "source": [
    "df5=pd.melt(df2,\n",
    "id_vars=[\"Date\"],\n",
    "value_vars=[\"Type\", \"Value\"],\n",
    "value_name=\"Observations\")\n",
    "print df5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "数据丢弃"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Capital</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Brussels</td>\n",
       "      <td>11190846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>New Delhi</td>\n",
       "      <td>1303171035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Brasília</td>\n",
       "      <td>207847528</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Capital  Population\n",
       "0   Brussels    11190846\n",
       "1  New Delhi  1303171035\n",
       "2   Brasília   207847528"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.drop(['a','c'])\n",
    "df.drop('Country', axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Capital</th>\n",
       "      <th>Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Country  Capital  Population\n",
       "0      1.0      2.0         1.0\n",
       "1      3.0      3.0         3.0\n",
       "2      2.0      1.0         2.0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_index()\n",
    "df.sort_values(by='Country')\n",
    "df.rank()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "获取数据信息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3 entries, 0 to 2\n",
      "Data columns (total 3 columns):\n",
      "Country       3 non-null object\n",
      "Capital       3 non-null object\n",
      "Population    3 non-null int64\n",
      "dtypes: int64(1), object(2)\n",
      "memory usage: 144.0+ bytes\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Country       3\n",
       "Capital       3\n",
       "Population    3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape\n",
    "df.index\n",
    "df.columns\n",
    "df.info()\n",
    "df.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "数据摘要"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Population    207847528.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sum()\n",
    "df.cumsum()\n",
    "#df.min()/df.max()\n",
    "#df.idxmin()/df.idxmax()\n",
    "df.describe()\n",
    "df.mean()\n",
    "df.median()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "选择"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "s['b']\n",
    "df[1:]\n",
    "\n",
    "df.iloc[[0],[0]]\n",
    "df.iat([0])\n",
    "\n",
    "#df.loc[[0], ['Country']]\n",
    "#df.at([0], ['Country'])\n",
    "\n",
    "df.ix[2]\n",
    "df.ix[:,'Capital']\n",
    "df.ix[1,'Capital']\n",
    "\n",
    "#Boolean Indexing\n",
    "s[~(s > 1)]\n",
    "s[(s < -1) | (s > 2)]\n",
    "df[df['Population']>1200000000]\n",
    "\n",
    "s['a'] = 6\n",
    "\n",
    "#Selecting\n",
    "df3.loc[:,(df3>1).any()]\n",
    "df3.loc[:,(df3>1).all()]\n",
    "df3.loc[:,df3.isnull().any()]\n",
    "df3.loc[:,df3.notnull().all()]\n",
    "#Indexing With isin\n",
    "df[(df.Country.isin(df2.Type))]\n",
    "df3.filter(items=[\"a\",\"b\"])\n",
    "df.select(lambda x: not x%5)\n",
    "#Where\n",
    "s.where(s > 0)\n",
    "#Query\n",
    "#df.query('second > first')\n",
    "\n",
    "#Setting/Resetting Index\n",
    "df.set_index('Country')\n",
    "df4 = df.reset_index()\n",
    "df = df.rename(index=str,columns={\"Country\":\"cntry\",\"Capital\":\"cptl\",\n",
    "                                  \"Population\":\"ppltn\"})\n",
    "s2 = s.reindex(['a','c','d','e','b'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  cntry cptl  ppltn\n",
      "0   NaN  NaN    NaN\n",
      "1   NaN  NaN    NaN\n",
      "2   NaN  NaN    NaN\n",
      "3   NaN  NaN    NaN\n"
     ]
    }
   ],
   "source": [
    "ss= df.reindex(range(4),method='ffill')\n",
    "print ss"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    6\n",
      "1    6\n",
      "2    6\n",
      "3    6\n",
      "4    6\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "s3 = s.reindex(range(5),method='bfill')\n",
    "print s3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "数据聚合"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   index  Country    Capital  Population\n",
      "0      0  Belgium   Brussels    11190846\n",
      "1      1    India  New Delhi  1303171035\n",
      "2      2   Brazil   Brasília   207847528\n"
     ]
    }
   ],
   "source": [
    "#Aggregation\n",
    "df2.groupby(by=['Date','Type']).mean()\n",
    "df4.groupby(level=0).sum()\n",
    "print df4\n",
    "#df4.groupby(level=0).agg({'Capital':lambda x:sum(x)/len(x), 'Population': np.sum})\n",
    "#Transformation\n",
    "#customSum = lambda x: (x+x%2)\n",
    "#df4.groupby(level=0).transform(customSum)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "帮助"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "help(pd.Series.loc)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "使用函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cntry</th>\n",
       "      <th>cptl</th>\n",
       "      <th>ppltn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BelgiumBelgium</td>\n",
       "      <td>BrusselsBrussels</td>\n",
       "      <td>22381692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>IndiaIndia</td>\n",
       "      <td>New DelhiNew Delhi</td>\n",
       "      <td>2606342070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BrazilBrazil</td>\n",
       "      <td>BrasíliaBrasília</td>\n",
       "      <td>415695056</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            cntry                cptl       ppltn\n",
       "0  BelgiumBelgium    BrusselsBrussels    22381692\n",
       "1      IndiaIndia  New DelhiNew Delhi  2606342070\n",
       "2    BrazilBrazil    BrasíliaBrasília   415695056"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f=lambda x:x*2\n",
    "df.apply(f)\n",
    "df.applymap(f)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "数据对齐"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    13.0\n",
       "b     NaN\n",
       "c     5.0\n",
       "d     7.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s3 = pd.Series([7, -2, 3], index=['a', 'c', 'd'])\n",
    "s + s3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "输入输出"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#读取和写入csv\n",
    "pd.read_csv('file.csv', header=None, nrows=5)\n",
    "df.to_csv('myDataFrame.csv')\n",
    "\n",
    "#读取和写入excel\n",
    "pd.read_excel('file.xlsx')\n",
    "pd.to_excel('dir/myDataFrame.xlsx', sheet_name='Sheet1')\n",
    "#从多个表单读取数据\n",
    "xlsx = pd.ExcelFile('file.xls')\n",
    "df = pd.read_excel(xlsx, 'Sheet1')\n",
    "\n",
    "#从SQL查询或者数据库表读取和写入数据\n",
    "from sqlalchemy import create_engine\n",
    "engine = create_engine('sqlite:///:memory:')\n",
    "pd.read_sql(\"SELECT * FROM my_table;\", engine)\n",
    "pd.read_sql_table('my_table', engine)\n",
    "pd.read_sql_query(\"SELECT * FROM my_table;\", engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "数据合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dict1 = {'X1': ['a', 'b', 'c'],\n",
    "'X2': ['11.432', '1.303', '99.906']}\n",
    "\n",
    "dict2 = {'X1': ['a', 'b', 'd'],\n",
    "'X3': ['20.784', 'NaN', '20.784']}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>X1</th>\n",
       "      <th>X2</th>\n",
       "      <th>X3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>11.432</td>\n",
       "      <td>20.784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>1.303</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c</td>\n",
       "      <td>99.906</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  X1      X2      X3\n",
       "0  a  11.432  20.784\n",
       "1  b   1.303     NaN\n",
       "2  c  99.906     NaN"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1 = pd.DataFrame(dict1,\n",
    "columns=['X1', 'X2'])\n",
    "data2 = pd.DataFrame(dict2,\n",
    "columns=['X1', 'X3'])\n",
    "\n",
    "pd.merge(data1,\n",
    "data2,\n",
    "how='left',\n",
    "on='X1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>X1</th>\n",
       "      <th>X2</th>\n",
       "      <th>X3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>11.432</td>\n",
       "      <td>20.784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>1.303</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>d</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.784</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  X1      X2      X3\n",
       "0  a  11.432  20.784\n",
       "1  b   1.303     NaN\n",
       "2  d     NaN  20.784"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(data1,\n",
    "data2,\n",
    "how='right',\n",
    "on='X1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>X1</th>\n",
       "      <th>X2</th>\n",
       "      <th>X3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>11.432</td>\n",
       "      <td>20.784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>1.303</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  X1      X2      X3\n",
       "0  a  11.432  20.784\n",
       "1  b   1.303     NaN"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(data1,\n",
    "data2,\n",
    "how='inner',\n",
    "on='X1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>X1</th>\n",
       "      <th>X2</th>\n",
       "      <th>X3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>11.432</td>\n",
       "      <td>20.784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>1.303</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c</td>\n",
       "      <td>99.906</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>d</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.784</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  X1      X2      X3\n",
       "0  a  11.432  20.784\n",
       "1  b   1.303     NaN\n",
       "2  c  99.906     NaN\n",
       "3  d     NaN  20.784"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(data1,\n",
    "data2,\n",
    "how='outer',\n",
    "on='X1')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    A key\n",
      "0  A0  K0\n",
      "1  A1  K1\n",
      "2  A2  K2\n",
      "3  A3  K3\n",
      "4  A4  K4\n",
      "5  A5  K5\n"
     ]
    }
   ],
   "source": [
    "#help(df.join)\n",
    "\n",
    "caller = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3', 'K4', 'K5'],\n",
    "                        'A': ['A0', 'A1', 'A2', 'A3', 'A4', 'A5']})\n",
    "other = pd.DataFrame({'key': ['K0', 'K1', 'K2'],\n",
    "                      'B': ['B0', 'B1', 'B2']})\n",
    "caller.join(other, lsuffix='_caller', rsuffix='_other')\n",
    "print caller\n",
    "#data1.set_index('X1')\n",
    "#data2.set_index('X1')\n",
    "#data1.join(data2, lsuffix='data1', rsuffix='data2', how='right')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    A key\n",
      "0  A0  K0\n",
      "1  A1  K1\n",
      "2  A2  K2\n",
      "3  A3  K3\n",
      "4  A4  K4\n",
      "5  A5  K5\n"
     ]
    }
   ],
   "source": [
    "caller = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3', 'K4', 'K5'],\n",
    "                        'A': ['A0', 'A1', 'A2', 'A3', 'A4', 'A5']})\n",
    "other = pd.DataFrame({'key': ['K0', 'K1', 'K2'],\n",
    "                      'B': ['B0', 'B1', 'B2']})\n",
    "caller.set_index('key').join(other.set_index('key'))\n",
    "print caller"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    A key\n",
      "0  A0  K0\n",
      "1  A1  K1\n",
      "2  A2  K2\n",
      "3  A3  K3\n",
      "4  A4  K4\n",
      "5  A5  K5\n"
     ]
    }
   ],
   "source": [
    "caller = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3', 'K4', 'K5'],\n",
    "                        'A': ['A0', 'A1', 'A2', 'A3', 'A4', 'A5']})\n",
    "other = pd.DataFrame({'key': ['K0', 'K1', 'K2'],\n",
    "                      'B': ['B0', 'B1', 'B2']})\n",
    "caller.join(other.set_index('key'), on='key')\n",
    "print caller"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
